# [src/util/FileUtil.py]
import json
from typing import List

import openpyxl
from model.Student import Student
from PySide6.QtWidgets import QFileDialog

class FileUtil:
    @staticmethod
    def get_excel_path(parent_widget, title="选择文件"):
        """获取Excel文件路径（静态方法）"""
        return QFileDialog.getOpenFileName(
            parent_widget, title, "", "Excel文件 (*.xlsx)"
        )[0]

    @staticmethod
    def load_students_records(student: Student, headers, row):
        for col_idx in range(3, len(headers)):
            try:
                # 直接强制转为字符串（无论headers[col_idx]是单元格对象还是字符串）
                date_key = str(headers[col_idx]).strip() if headers[col_idx] is not None else ""
                
                # 空日期列名终止读取
                if not date_key:
                    break
                    
                # 处理数据单元格（保持原有逻辑）
                cell_value = row[col_idx]
                if cell_value and isinstance(cell_value, str):  # 仅处理非空字符串
                    # print(f"原始数据[列{col_idx}]: {repr(cell_value)}")  # 显示原始字符串
                    # print(f"日期键: {date_key}")  # 检查日期格式是否一致
                    date_record = json.loads(cell_value) if cell_value else {}
                    # print(f"json:{date_record}")
                    student.update_behavior_record(date_key, date_record)
                    
            except Exception as e:  # 捕获所有可能的异常
                print(f"列处理异常（列:{col_idx} 日期:{date_key}）: {e}")

    @staticmethod 
    def load_students(sheet):
        """修改后的主加载方法"""
        students = []
        headers = [cell.value for cell in sheet[1]]
        
        for row_idx, row in enumerate(sheet.iter_rows(min_row=2, values_only=True), start=2):
            if not row[0]:
                continue
                
            student = Student(
                stu_id=str(row[0]).strip(),
                name=str(row[1]).strip() if row[1] else "未命名",
                gender=str(row[2]).strip() if len(row)>2 and row[2] else "未填写"
            )
            
            FileUtil.load_students_records(student, headers, row)  # 加载学生行为记录
            students.append(student)
            
        return students

    @staticmethod
    def load_behavior(sheet="behavior"):
        """从Excel表读取行为配置（静态方法）"""
        behaviors = []
        for row in sheet.iter_rows(min_row=2, values_only=True):
            if not row[0]:
                continue
            try:
                behaviors.append({
                    "name": str(row[0]).strip(),
                    "attr": str(row[1]).strip() if len(row)>1 and row[1] else "",
                    "value": float(row[2]) if len(row)>2 and row[2] is not None else 0
                })
            except Exception as e:
                print(f"行为数据解析失败（行内容：{row}）: {e}")
        return behaviors

    @staticmethod
    def validate_behavior_record(record):
        """验证行为记录JSON结构"""
        return all(k in record for k in ["seat", "behavior"])

    @staticmethod
    def save_students_file(students: List['Student'], file_path: str) -> bool:
        """
        修正版学生数据保存方法
        关键改进：
        1. 修复JSON序列化导致的格式损坏
        2. 增强类型安全检查
        3. 优化Excel单元格类型处理
        """
        try:
            # === 1. 参数验证 ===
            if not isinstance(students, list):
                raise TypeError("students参数必须是列表")
            if not all(hasattr(s, 'date_records') for s in students):
                raise ValueError("学生对象必须包含date_records属性")

            # === 2. 准备Excel文件 ===
            wb = openpyxl.load_workbook(file_path)
            ws = wb["students"]
            
            # 清空旧数据（保留表头）
            if ws.max_row > 1:
                ws.delete_rows(2, ws.max_row - 1)

            # === 3. 处理日期列 ===
            # 收集所有日期并排序
            all_dates = sorted({
                date for student in students 
                for date in student.date_records.keys()
                if date  # 过滤空日期
            })

            # 更新表头（前三列固定+日期列）
            headers = ["学号", "姓名", "性别"] + all_dates
            for col, header in enumerate(headers, 1):
                ws.cell(row=1, column=col, value=header)

            # === 4. 写入学生数据 ===
            for row_idx, student in enumerate(students, 2):
                # 基础信息
                ws.cell(row=row_idx, column=1, value=str(student.stu_id))
                ws.cell(row=row_idx, column=2, value=str(student.name))
                ws.cell(row=row_idx, column=3, value=str(student.gender))

                # 日期列数据
                for col_idx, date in enumerate(all_dates, 4):
                    if date in student.date_records:
                        record = student.date_records[date]
                        if not isinstance(record, dict):
                            print(f"警告：{student.name}的{date}记录不是字典类型")
                            continue

                        # 安全生成JSON字符串
                        json_str = json.dumps(
                            record,
                            ensure_ascii=False,
                            separators=(',', ':'),
                            indent=None
                        )
                        
                        # 写入单元格（显式声明文本格式）
                        cell = ws.cell(row=row_idx, column=col_idx)
                        cell.value = json_str
                        cell.number_format = '@'  # 强制文本格式

            # === 5. 保存文件 ===
            wb.save(file_path)
            return True

        except Exception as e:
            print(f"保存失败: {str(e)}", exc_info=True)
            return False
